/*
 * DatabaseAdapter.java version v1.01
 * v1.02: executeSelectQuery does not return a ResultSet object anymore but a Collection of Object[]
 */

package fr.mywiki.model.queries;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import fr.mywiki.business.log.LogManager;


/**
 * This service deals with all kinds of queries.
 * 
 * @author Thomas
 */

public class DatabaseAdapter {
	public static final String DRIVER_PATH = "com.mysql.jdbc.Driver";
	public static final String LOGIN = "root";
	public static final String PASSWORD = "";

	public static final String CONNECTION_URL = "jdbc:mysql://localhost/mywiki?user=" + LOGIN
			+ "&password=" + PASSWORD;

	/**
	 * Initializes the adapter by launching the driver.
	 */
	public static void init() throws ClassNotFoundException {
		loadDriver();
	}

	/**
	 * Opens a connection to the database, prepares a statement and executes it, then closes the
	 * connection. The query is a complete update query, i.e. a query with INSERT, UPDATE or DELETE
	 * and must be written in complete SQL language.
	 * 
	 * @param sql the SQL query to execute.
	 * @throws SQLException if a problem occured trying to execute this query.
	 */
	public synchronized static void executeUpdateQuery(String sql) throws SQLException {
		Connection con = makeConnection();
		PreparedStatement stat = con.prepareStatement(sql);
		LogManager.println(sql);
		stat.executeUpdate();
		closeConnection(con);
	}
	
	/**
	 * Opens a connection to the database, prepares a statement and executes it, then closes the
	 * connection. The query is a complete update query, i.e. a query with INSERT, UPDATE or DELETE
	 * and must be written in complete SQL language.
	 * 
	 * @param sql the SQL query to execute.
	 * @param arguments arguments to be put in the query.
	 * @throws SQLException if a problem occured trying to execute this query.
	 */
	public synchronized static void executeUpdateQuery(String sql,
			List arguments) throws SQLException {
		Connection con = makeConnection();
		PreparedStatement stat = con.prepareStatement(sql);
		
		for (int i = 0; i < arguments.size(); i++)
			stat.setObject(i, arguments.get(i));
				
		LogManager.println(sql);
		stat.executeUpdate();
		closeConnection(con);
	}

	/**
	 * Opens a connection to the database, prepares a statement and executes it, then closes the
	 * connection and returns the result. The query is a complete select query.
	 * 
	 * @param sql the SQL query to execute.
	 * @return a ArrayList object containing Object[] arrays corresponding to the result records of the query.
	 * @throws SQLException if a problem occured trying to execute this query.
	 */
	public synchronized static ResultList executeSelectQuery(String sql) throws SQLException {
		Connection con = makeConnection();
		PreparedStatement stat = con.prepareStatement(sql);
		ResultSet resultSet = stat.executeQuery();
		
		ResultList list = new ResultList(resultSet); 
		
		closeConnection(con);
		return list;
	}

	/**
	 * Loads the driver.
	 * 
	 * @throws ClassNotFoundException if the driver cannot be found.
	 */
	private static void loadDriver() throws ClassNotFoundException {
		Class.forName(DRIVER_PATH);
	}

	/**
	 * Creates a new connection to the database.
	 * 
	 * @throws SQLException if the connection could not be done.
	 */
	protected static Connection makeConnection() throws SQLException {
		return DriverManager.getConnection(CONNECTION_URL);
	}

	/**
	 * Closes the connection to the database.
	 * 
	 * @throws SQLException if the connection could not be closed
	 */
	protected static void closeConnection(Connection con) throws SQLException {
		con.close();
	}
	
	/**
	 * Executes the query given in parameter and extracts a blob from the first
	 * column of the first record returned.
	 * 
	 * @param sql a sql request.
	 * @return a byte array.
	 * @throws SQLException if the query could not be correctly executed.
	 */
	public synchronized static byte[] extractByteArray(String sql)
			throws SQLException {
		Connection con = makeConnection();
		PreparedStatement stat = con.prepareStatement(sql);
		ResultSet resultSet = stat.executeQuery();

		Blob blob = resultSet.getBlob(1);
		return blob.getBytes(1L, (int) blob.length());
	}
}